﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace AspAuthentication.Persistence.DatabaseScripts
{
    /// <summary>
    /// Defines the class TableCreator
    /// </summary>
    public static class TableCreator
    {
        /// <summary>
        /// Creates the tables if not exist.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="tableNames">The table names.</param>
        public static void CreateTablesIfNotExist(string connectionString, IList<string> tableNames)
        {
            foreach (var tableName in tableNames.Where(tableName => !TableExist(connectionString, tableName)))
            {
                CreateTable(connectionString, tableName);
            }
        }

        /// <summary>
        /// Creates the table if not exist.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="tableName">Name of the table.</param>
        public static void CreateTableIfNotExist(string connectionString, string tableName)
        {
            if (!TableExist(connectionString, tableName))
            {
                CreateTable(connectionString, tableName);
            }
        }
        /// <summary>
        /// Tables the exist.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="tableName">Name of the table.</param>
        /// <returns></returns>
        public static bool TableExist(string connectionString, string tableName)
        {
            bool result;
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                try
                {
                    var command = new SqlCommand(string.Format("Select count(*) From {0}", tableName), connection);
                    command.ExecuteScalar();
                    result = true;//if the table does not exist an error will be thrown
                }
                catch
                {
                    result = false;
                }
                finally
                {
                    connection.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// Creates the table.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="resourceName">Name of the resource.</param>
        public static void CreateTable(string connectionString, string resourceName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                try
                {
                    var command = new SqlCommand(ExctractResource(resourceName), connection);
                    command.ExecuteNonQuery();
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Creates the stored procedure.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="procedures">The procedures.</param>
        public static void CreateStoredProcedure(string connectionString, IList<string> procedures)
        {
            CreateProcedure(connectionString, "DropStoredProcedures");
            foreach (var entry in procedures)
            {
                CreateProcedure(connectionString, entry);
            }
        }

        private static void CreateProcedure(string connectionString, string resourceName)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                try
                {
                    var command = new SqlCommand(ExctractResource(resourceName), connection);
                    command.ExecuteNonQuery();
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private static string ExctractResource(string resourceName)
        {
            var content = new StringBuilder();
            using (var stream = Assembly.GetExecutingAssembly()
                    .GetManifestResourceStream(
                    string.Format("AspAuthentication.Persistence.DatabaseScripts.{0}.txt", resourceName)))
            {
                var streamReader = new StreamReader(stream);
                content.Append(streamReader.ReadToEnd());
            }

            return content.ToString();
        }
    }
}
